-- Demo script for showing how NULL values work in comparisons.
-- © Erland Sommarskog 2020.

USE SQLTitbits;                         
go
-- Look at the Customers table. Note that some customers have a CustRespID,
-- and some have not.
SELECT * FROM dbo.Customers;
go
-- Wrong queries to find customers that don't/do have a customer responsible.
SELECT * FROM dbo.Customers WHERE CustRespID = NULL; 
SELECT * FROM dbo.Customers WHERE CustRespID <> NULL;
go
-- You need to use IS NULL.
SELECT * FROM dbo.Customers WHERE CustRespID IS NULL; 
SELECT * FROM dbo.Customers WHERE CustRespID IS NOT NULL;
go
-- No different with a variable.
DECLARE @RespID int = NULL
SELECT * FROM dbo.Customers WHERE CustRespID = @RespID
go

-- Or if you join.
DECLARE @resps TABLE (RespID smallint NULL);
INSERT @resps (RespID) VALUES(4), (7), (NULL);
SELECT C.CustomerID, C.CustomerName, C.City, C.CustRespID
FROM   dbo.Customers C
JOIN   @resps r ON C.CustRespID = r.RespID;
go

